import pymysql
from keyWords import sentence_filter

from flask import Flask, render_template, request, url_for, flash, redirect

app = Flask(__name__)
app.config['SECRET_KEY'] = 'hello world'


# 创建一个函数用来获取数据库链接
def get_db_connection():
    # 创建数据库链接到database.db文件
    conn = pymysql.connect(host='localhost', user='root', password='123456', database='main', charset='utf8', cursorclass = pymysql.cursors.DictCursor)
    return conn


# 根据post_id从数据库中获取post
def get_post(post_id):
    conn = get_db_connection()
    cur = conn.cursor()
    sql = 'SELECT * FROM posts WHERE id = %s'
    cur.execute(sql, (post_id,))
    post = cur.fetchone()
    cur.close()
    return post


@app.route('/')
def index():
    # 调用上面的函数，获取链接
    conn = get_db_connection()
    cur = conn.cursor()
    sql = 'SELECT * FROM posts'
    cur.execute(sql)
    posts = cur.fetchall()
    cur.close()
    # 把查询出来的posts传给网页
    return render_template('index.html', posts=posts)


@app.route('/posts/<int:post_id>', methods=('GET', 'POST'))
def post(post_id):
    post = get_post(post_id)
    if request.method == 'POST':
        comment = request.form['comment']
        rex_ct = sentence_filter(comment)
        if not comment:
            flash('评论不能为空！')
        elif rex_ct is not None:
            flash('存在敏感词！请修改！')
        else:
            conn = get_db_connection()
            cur = conn.cursor()
            cur.execute('INSERT INTO comments (post, comment) VALUES (%s, %s)', (post_id, comment))
            conn.commit()
            cur.close()
    conn = get_db_connection()
    cur = conn.cursor()
    sql = 'SELECT * FROM comments WHERE post = %s'
    cur.execute(sql,  (post_id))
    comments = cur.fetchall()
    cur.close()
    return render_template('post.html', post=post, comments=comments)


@app.route('/posts/new', methods=('GET', 'POST'))
def new():
    if request.method == 'POST':
        title = request.form['title']
        content = request.form['content']
        rex_t = sentence_filter(title)
        rex_c = sentence_filter(content)
        if not title:
            flash('标题不能为空!')
        elif not content:
            flash('内容不能为空')
        elif rex_t is not None or rex_c is not None:
            flash('存在敏感词！请修改！')
        else:
            conn = get_db_connection()
            cur = conn.cursor()
            cur.execute('INSERT INTO posts (title, content) VALUES (%s, %s)', (title, content))
            conn.commit()
            cur.close()
            return redirect(url_for('index'))

    return render_template('new.html')


@app.route('/posts/<int:id>/edit', methods=('GET', 'POST'))
def edit(id):
    post = get_post(id)

    if request.method == 'POST':
        title = request.form['title']
        content = request.form['content']

        if not title:
            flash('Title is required!')
        else:
            conn = get_db_connection()
            cur = conn.cursor()
            cur.execute('UPDATE posts SET title = %s, content = %s'
                         ' WHERE id = %s',
                         (title, content, id))
            conn.commit()
            cur.close()
            return redirect(url_for('index'))

    return render_template('edit.html', post=post)


@app.route('/posts/<int:id>/delete', methods=('POST',))
def delete(id):
    post = get_post(id)
    conn = get_db_connection()
    cur = conn.cursor()
    cur.execute('DELETE FROM posts WHERE id = %s', (id,))
    conn.commit()
    cur.close()
    flash('"{}" 删除成功!'.format(post['title']))
    return redirect(url_for('index'))


@app.route('/about')
def about():
    return render_template('about.html')
